"""
Case Type   : HTAP行列融合特性
Case Name   : HTAP行列融合特性--一级分区表-范围分区表
Create At   : 2025/11/1
Owner       : sungang14
Description :
    1.建表
    2.插入数据
    3.全表转换
    4.主备查询
    5.取消转换
    6.部分列转换
    7.主备查询
    8.取消转换
    9.清理环境
Expect      :
    1.执行成功
    2.执行成功
    3.执行成功
    4.查询成功
    5.执行成功
    6.执行成功
    7.查询成功
    8.执行成功
    9.执行成功
History     :
"""

import os
import unittest
import time

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger
from testcase.utils.Constant import Constant
from yat.test import macro


class HTAPIntegerTypes(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.sta1_sh = CommonSH('Standby1DbUser')
        self.sta2_sh = CommonSH('Standby2DbUser')
        self.constant = Constant()
        self.pg_hba_backup = None
        text = '----备份pg_hba.conf文件;expect:执行成功----'
        self.log.info(text)
        cmd = f'source {macro.DB_ENV_PATH};' \
              f'cp {macro.DB_INSTANCE_PATH}/pg_hba.conf {macro.DB_INSTANCE_PATH}/pg_hba.conf.backup.test'
        self.log.info(cmd)
        exec_res = self.pri_sh.node.sh(cmd).result()
        self.log.info(exec_res)

        text = '----配置主机信任连接;expect:执行成功----'
        self.log.info(text)
        cmd = f'source {macro.DB_ENV_PATH};' \
              f'gs_guc reload -N all -I all -h "host all all {self.pri_sh.node.db_host}/32 trust";' \
              f'gs_guc reload -N all -I all -h "host all all {self.sta1_sh.node.db_host}/32 trust";' \
              f'gs_guc reload -N all -I all -h "host all all {self.sta2_sh.node.db_host}/32 trust"'
        self.log.info(cmd)
        exec_res = self.pri_sh.node.sh(cmd).result()
        self.log.info(exec_res)
        self.table_name = 't_htap_0003'

    def test_integer_types_htap(self):
        text = '----step1:建表;expect:执行成功----'
        self.log.info(text)
        sql_cmd = f'drop table if exists {self.table_name} cascade;'
        res = self.pri_sh.execut_db_sql(sql_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, res, f'执行失败:{text}')
        sql = f"""create table {self.table_name}(
                c1 integer(20),
                c2 varchar(20) not null default '',
                c3 char(30)
                )
                partition by range (c1)
                (
                    partition p1 values less than (1000),
                    partition p2 values less than (2000),
                    partition p3 values less than (3000),
                    partition p4 values less than (maxvalue)
                );"""
        q_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(q_res)
        self.assertEqual(self.constant.TABLE_CREATE_SUCCESS, q_res, f'执行失败:{text}')

        text = '----step2:插入数据;expect:执行成功----'
        self.log.info(text)
        sql = f"insert into {self.table_name} values (generate_series(1, 10000), generate_series(1, 10000)||'aaa', generate_series(1, 10000)||'bbb');"
        q_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(q_res)
        self.assertEqual(f"INSERT 0 10000", q_res, f'执行失败:{text}')
        self.sql_select = f"select count(*) from {self.table_name} where c1 between 299 and 5999;"
        q_res = self.pri_sh.execut_db_sql(self.sql_select)
        self.log.info(q_res)
        self.assertIn(f" 5701\n(1 row)", q_res, f'执行失败:{text}')

        text = '----step3:全表转换;expect:执行成功----'
        self.log.info(text)
        sql = f"alter table {self.table_name} imcstored;"
        q_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(q_res)
        self.assertEqual(self.constant.ALTER_TABLE_MSG, q_res, f'执行失败:{text}')

        text = '----step4:主备查询;expect:查询成功----'
        self.log.info(text)
        q_res = self.pri_sh.execut_db_sql(self.sql_select)
        self.log.info(f'主节点查询结果:\n{q_res}')
        self.assertIn(f" 5701\n(1 row)", q_res, f'执行失败:{text}')

        self.sql_select1 = f"set enable_imcsscan to on;" \
                           f"select count(*) from {self.table_name} where c1 between 299 and 5999;"
        q_res = self.sta1_sh.execut_db_sql(self.sql_select1)
        self.log.info(f'备节点查询结果:\n{q_res}')
        self.assertIn(f" 5701\n(1 row)", q_res, f'执行失败:{text}')

        text = '----step5:取消转换;expect:执行成功----'
        self.log.info(text)
        sql = f"alter table {self.table_name} unimcstored;"
        q_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(q_res)
        self.assertEqual(self.constant.ALTER_TABLE_MSG, q_res, f'执行失败:{text}')

        text = '----step6:部分列转换;expect:执行成功----'
        self.log.info(text)
        sql = f"alter table {self.table_name} imcstored(c1, c3);"
        q_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(q_res)
        self.assertEqual(self.constant.ALTER_TABLE_MSG, q_res, f'执行失败:{text}')

        text = '----step7:主备查询;expect:查询成功----'
        self.log.info(text)
        q_res = self.pri_sh.execut_db_sql(self.sql_select)
        self.log.info(f'主节点查询结果:\n{q_res}')
        self.assertIn(f" 5701\n(1 row)", q_res, f'执行失败:{text}')
        q_res = self.sta1_sh.execut_db_sql(self.sql_select1)
        self.log.info(f'备节点查询结果:\n{q_res}')
        self.assertIn(f" 5701\n(1 row)", q_res, f'执行失败:{text}')

        text = '----step8:取消转换;expect:执行成功----'
        self.log.info(text)
        sql = f"alter table {self.table_name} unimcstored;"
        q_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(q_res)
        self.assertEqual(self.constant.ALTER_TABLE_MSG, q_res, f'执行失败:{text}')

        text = '----step9:指定分区转换; expect:执行成功----'
        self.log.info(text)
        sql = f"alter table {self.table_name} modify partition p3 imcstored;"
        q_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(q_res)
        self.assertEqual(self.constant.ALTER_TABLE_MSG, q_res, f'执行失败:{text}')

        text = '----step10:主备查询;expect:查询成功----'
        self.log.info(text)
        self.sql_select2 = f"select count(*) from {self.table_name} where c1 between 2100 and 2999;"
        self.sql_select3 = f"set enable_imcsscan to on;" \
                           f"select count(*) from {self.table_name} where c1 between 2100 and 2999;"
        q_res = self.pri_sh.execut_db_sql(self.sql_select2)
        self.log.info(f'主节点查询结果:\n{q_res}')
        self.assertIn(f" 900\n(1 row)", q_res, f'执行失败:{text}')
        q_res = self.sta1_sh.execut_db_sql(self.sql_select3)
        self.log.info(f'备节点查询结果:\n{q_res}')
        self.assertIn(f" 900\n(1 row)", q_res, f'执行失败:{text}')

        text = '----step11:取消转换;expect:执行成功----'
        self.log.info(text)
        sql = f"alter table {self.table_name} modify partition p3 unimcstored;"
        q_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(q_res)
        self.assertEqual(self.constant.ALTER_TABLE_MSG, q_res, f'执行失败:{text}')

        text = '----step12:指定分区的部分列转换; expect:执行成功----'
        self.log.info(text)
        sql = f"alter table {self.table_name} modify partition p2 imcstored(c1,c3);"
        q_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(q_res)
        self.assertEqual(self.constant.ALTER_TABLE_MSG, q_res, f'执行失败:{text}')

        text = '----step13:主备查询;expect:查询成功----'
        self.log.info(text)
        self.sql_select4 = f"select count(*) from {self.table_name} where c1 between 1199 and 1999;"
        self.sql_select5 = f"set enable_imcsscan to on;" \
                           f"select count(*) from {self.table_name} where c1 between 1199 and 1999;"
        q_res = self.pri_sh.execut_db_sql(self.sql_select4)
        self.log.info(f'主节点查询结果:\n{q_res}')
        self.assertIn(f" 801\n(1 row)", q_res, f'执行失败:{text}')
        q_res = self.sta1_sh.execut_db_sql(self.sql_select5)
        self.log.info(f'备节点查询结果:\n{q_res}')
        self.assertIn(f" 801\n(1 row)", q_res, f'执行失败:{text}')

        text = '----step14:取消转换;expect:执行成功----'
        self.log.info(text)
        sql = f"alter table {self.table_name} modify partition p2 unimcstored;"
        q_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(q_res)
        self.assertEqual(self.constant.ALTER_TABLE_MSG, q_res, f'执行失败:{text}')

    def tearDown(self):
        text = '----step15:清理环境;expect:执行成功----'
        self.log.info(text)
        sql_cmd = f'drop table if exists {self.table_name} cascade;'
        res = self.pri_sh.execut_db_sql(sql_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, res, f'执行失败:{text}')

        text = '----恢复pg_hba.conf文件;expect:执行成功----'
        self.log.info(text)
        cmd = f'source {macro.DB_ENV_PATH};' \
              f'cp {macro.DB_INSTANCE_PATH}/pg_hba.conf.backup.test {macro.DB_INSTANCE_PATH}/pg_hba.conf;' \
              f'gs_om -t restart'
        self.log.info(cmd)
        exec_res = self.pri_sh.node.sh(cmd).result()
        self.log.info(exec_res)
        time.sleep(30)
        cmd = f'source {macro.DB_ENV_PATH};gs_om -t status --detail'
        status_res = self.pri_sh.node.sh(cmd).result()
        self.log.info(f'数据库集群状态:\n{status_res}')
        cmd = f'source {macro.DB_ENV_PATH};rm -f {macro.DB_INSTANCE_PATH}/pg_hba.conf.backup.test'
        self.pri_sh.node.sh(cmd).result()
        self.log.info('pg_hba.conf备份文件已清理')

        self.log.info(f'-----{os.path.basename(__file__)} end-----')
